package com.hex.ds.hdtp.core.inf.metadata.service.impl;

import cn.hutool.core.util.StrUtil;
import com.hex.ds.hdtp.core.inf.metadata.converter.IMysqlSqlResultMapper;
import com.hex.ds.hdtp.core.inf.metadata.entity.TableFieldsEntity;
import com.hex.ds.hdtp.core.inf.metadata.repository.ISourceConfInfoRepository;
import org.springframework.stereotype.Service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Arrays;
import java.util.List;
import java.util.Locale;
import java.util.Map;

/**
 * @Package com.hex.ds.hdtp.core.inf.metadata.service.impl
 * @ClassName MysqlMethodService
 * @Description MySQL元数据管理服务类
 * @Author gj.xu
 * @Date 2024/3/4 18:21
 * @Version v2.0
 **/
@Service("MYSQL")
public class MysqlMethodService extends AbstractSourceMethodService {

    private final IMysqlSqlResultMapper mysqlSqlResultMapper;

    public MysqlMethodService(ISourceConfInfoRepository sourceConfInfoRepository, IMysqlSqlResultMapper mysqlSqlResultMapper) {
        super(sourceConfInfoRepository);
        this.mysqlSqlResultMapper = mysqlSqlResultMapper;
    }

    @Override
    protected String queryProceduresContentSql(String schemaName, String proceduresName) {
        String query = "SELECT ROUTINE_DEFINITION " +
                "FROM INFORMATION_SCHEMA.ROUTINES " +
                "WHERE ROUTINE_TYPE = 'PROCEDURE' " +
                "AND ROUTINE_SCHEMA = `{}` " +
                "AND ROUTINE_NAME = `{}`";
        return StrUtil.format(query, schemaName, proceduresName);
    }

    @Override
    protected String getProcedureContentFromResultSet(ResultSet resultSet) throws SQLException {
        return resultSet.getString("ROUTINE_DEFINITION");
    }

    @Override
    protected TableFieldsEntity toTableFieldsBean(Map<String, String> tableFieldsMap) {
        return mysqlSqlResultMapper.toTableFieldsBean(tableFieldsMap);
    }

    @Override
    protected String queryTableStructureSql(String schemaName, String tableName) {
        String querySql = "SELECT\n" +
                "  A.ORDINAL_POSITION,\n" +
                "  A.COLUMN_NAME,\n" +
                "  SUBSTRING_INDEX(A.COLUMN_TYPE, '(', 1) AS DATA_TYPE,\n" +
                "  CASE\n" +
                "    WHEN A.COLUMN_TYPE IN ('date', 'TIME', 'YEAR', 'DATETIME', 'TIMESTAMP','text') THEN '' -- 设置 COLUMN_LENGTH 为 ''（常量值）\n" +
                "    ELSE SUBSTRING_INDEX(SUBSTRING_INDEX(A.COLUMN_TYPE, '(', -1), ')', 1) -- 提取字段长度\n" +
                "  END AS columnLength,\n" +
                "  A.COLUMN_COMMENT,\n" +
                "  B.TABLE_SCHEMA,\n" +
                "  B.TABLE_NAME,\n" +
                "  B.TABLE_COMMENT,\n" +
                "  IF(C.CONSTRAINT_NAME IS NOT NULL, 'Y', 'N') AS isPrimaryKey, -- 判断字段是否为主键\n" +
                "  CASE\n" +
                "    WHEN A.IS_NULLABLE = 'YES' THEN 'Y'\n" +
                "    ELSE 'N'\n" +
                "  END AS isAllowNull -- 判断字段是否允许为空\n" +
                "FROM\n" +
                "  INFORMATION_SCHEMA.COLUMNS A\n" +
                "  LEFT JOIN INFORMATION_SCHEMA.TABLES B ON A.TABLE_SCHEMA = B.TABLE_SCHEMA\n" +
                "    AND A.TABLE_NAME = B.TABLE_NAME\n" +
                "  LEFT JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE C ON A.TABLE_SCHEMA = C.TABLE_SCHEMA\n" +
                "    AND A.TABLE_NAME = C.TABLE_NAME\n" +
                "    AND A.COLUMN_NAME = C.COLUMN_NAME\n" +
                "    AND C.CONSTRAINT_NAME = 'PRIMARY'\n" +
                "WHERE\n" +
                "  A.TABLE_SCHEMA = '{}'\n" +
                "  AND A.TABLE_NAME = '{}'\n" +
                "ORDER BY\n" +
                "  A.ORDINAL_POSITION;";
        return StrUtil.format(querySql, schemaName, tableName);
    }

    /**
     * @Method getStrDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取字符数据类型
     * @Author gj.xu
     * @Date 2024/2/21 14:20
     * @Version V2.0
     */
    @Override
    public List<String> getStrDataType() {
        return Arrays.asList("CHAR", "VARCHAR2", "CLOB", "NCHAR", "NVARCHAR2", "NCLOB", "LONG", "RAW", "BLOB", "BFILE");
    }

    /**
     * @Method getNumDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取数值数据类型
     * @Author gj.xu
     * @Date 2024/2/21 15:10
     * @Version V2.0
     */
    @Override
    public List<String> getNumDataType() {
        return Arrays.asList("NUMBER", "INT", "BINARY_FLOAT", "BINARY_DOUBLE", "FLOAT", "DECIMAL", "INTEGER", "SMALLINT", "BIGINT");
    }

    /**
     * @Method getAccuracyDataType
     * @Param
     * @Return java.util.List<java.lang.String>
     * @Description 获取精度数据类型
     * @Author gj.xu
     * @Date 2024/2/21 15:32
     * @Version V2.0
     */
    @Override
    public List<String> getAccuracyDataType() {
        return Arrays.asList("NUMBER", "FLOAT", "BINARY_FLOAT", "BINARY_DOUBLE", "DECIMAL");
    }

    /**
     * @Method getTableDataNumSql
     * @Param schema
     * @Param table
     * @Param whereSql
     * @Return java.lang.String
     * @Description 获取表的数据量SQL
     * @Author gj.xu
     * @Date 2024/1/29 15:28
     * @Version V2.0
     */
    @Override
    public String getTableDataNumSql(String schema, String table, String whereSql) {
        String querySql = "SELECT COUNT(*) FROM {}.{} {}";
        return StrUtil.format(querySql, schema.toLowerCase(Locale.ENGLISH), table.toLowerCase(Locale.ENGLISH), whereSql);
    }

    /**
     * @Method getTableAllFieldNumSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取所有字段总数SQL
     * @Author gj.xu
     * @Date 2024/2/1 11:12
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldNumSql(String schema, String table) {
        return getTableDescribe(schema, table);
    }

    /**
     * @Method getTableAllFieldNameSql
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取所有字段名称SQL
     * @Author gj.xu
     * @Date 2024/2/1 11:44
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldNameSql(String schema, String table) {
        return getTableDescribe(schema, table);
    }

    /**
     * @Method getTableDescribe
     * @Param schema
     * @Param table
     * @Return java.lang.String
     * @Description 获取Describe语句
     * @Author gj.xu
     * @Date 2024/3/11 15:35
     * @Version V2.0
     */
    private String getTableDescribe(String schema, String table) {
        String querySql = "DESCRIBE {}.{} ";
        return StrUtil.format(querySql, schema.toLowerCase(Locale.ENGLISH), table.toLowerCase(Locale.ENGLISH));
    }

    /**
     * @Method getTableAllFieldSql
     * @Param fields
     * @Param schema
     * @Param table
     * @Param partitionsWhereSql
     * @Param sortField
     * @Return java.lang.String
     * @Description 获取全字段明细对比SQL
     * @Author gj.xu
     * @Date 2024/1/30 18:14
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldSql(String fields, String schema, String table, String partitionsWhereSql, String sortField) {
        String querySql = "SELECT {} FROM {}.{} {} ORDER BY {}";
        return StrUtil.format(querySql, fields.toLowerCase(Locale.ENGLISH), schema.toLowerCase(Locale.ENGLISH), table.toLowerCase(Locale.ENGLISH), partitionsWhereSql, sortField.toLowerCase(Locale.ENGLISH));
    }

    /**
     * @Method getTableAllFieldSqlLimitSql
     * @Param fields
     * @Param schema
     * @Param table
     * @Param whereSql
     * @Param extractScope
     * @Param offset
     * @Param sortField
     * @Return java.lang.String
     * @Description 获取分批比对全字段明细对比的SQL
     * @Author gj.xu
     * @Date 2024/2/1 20:53
     * @Version V2.0
     */
    @Override
    public String getTableAllFieldSqlLimitSql(String fields, String schema, String table, String whereSql, Long extractScope, Long offset, String sortField) {
        String querySql = "SELECT {} FROM {}.{} {} ORDER BY {} limit {} offset {}";
        return StrUtil.format(querySql, fields.toLowerCase(Locale.ENGLISH), schema.toLowerCase(Locale.ENGLISH), table.toLowerCase(Locale.ENGLISH), whereSql, sortField.toLowerCase(Locale.ENGLISH), extractScope, offset);
    }
}
